/**
 * FoodGroupDAO.java
 * Nov 7, 2013
 * Copyright: phongtdse60732@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBUltilities.ConnectDB;
import DTO.FoodGroupDTO;

/**
 * @author trandophong
 * @version 1.0
 */
public class FoodGroupDAO {
	public void get(FoodGroupDTO fg) {
		if (fg.getId() != 0) {
			Connection conn = null;
			PreparedStatement preparedStatement = null;

			String sqlStatement = "SELECT * FROM FOODGROUP WHERE ID = ? ";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				preparedStatement.setInt(1, fg.getId());
				ResultSet rs = preparedStatement.executeQuery();

				while (rs.next()) {
					fg.setId((Integer) rs.getObject(1));
					fg.setName(new String(rs.getObject(2) == null ? "" : rs
							.getObject(2).toString()));
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
		}
	}

	public void add(FoodGroupDTO fg) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO FOODGROUP (NAME, DESCRIPTION) VALUES"
				+ "(?, ?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement,
					Statement.RETURN_GENERATED_KEYS);

			preparedStatement.setString(1, fg.getName());
			preparedStatement.setString(2, fg.getDescription());

			preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				System.out.println(rs.getInt(1));
			}
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	public void update(FoodGroupDTO fg) {

		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "UPDATE FOODGROUP SET NAME = ?, DESCRIPTION = ? WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setString(1, fg.getName());
			preparedStatement.setString(2, fg.getDescription());
			preparedStatement.setInt(3, fg.getId());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	public void delete(int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM FOODGROUP WHERE ID = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	public ArrayList<FoodGroupDTO> select() {
		Connection conn = null;
		Statement statement = null;
		ArrayList<FoodGroupDTO> list = new ArrayList<FoodGroupDTO>();

		String sqlStatement = "SELECT * FROM FOODGROUP";
		try {
			conn = ConnectDB.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlStatement);
			int id;
			String name;
			String description;

			while (rs.next()) {
				id = (Integer) rs.getObject(1);
				name = new String(rs.getObject(2) == null ? "" : rs
						.getObject(2).toString());
				description = rs.getObject(3) == null ? "" : rs.getObject(3)
						.toString();

				FoodGroupDTO footGroupItem = new FoodGroupDTO(id, name,
						description);
				list.add(footGroupItem);
			}
		} catch (Exception e) {
			return null;
		}
		return list;
	}
}
